package com.supermap.dataservice.project.mapper;


import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description 农情收藏
 * @date 2018年11月01 上午 10:36
 * @email 1301584033@qq.com
 */
@Mapper
public interface AgrCollectMapper {
    /**
     * 收藏
     */
    @Insert("insert into t_agricollect(userId,agrId) values(#{userId},#{agrId})")
    Integer putAgrCollect(@Param("userId") Integer userId, @Param("agrId") Integer agrId);

    /**
     * 是否已经被收场了
     */
    @Select("select count(1) from t_agricollect t where t.userId=#{userId} and agrId=#{agrId} ")
    Integer isBindUserAgrInfo(@Param("userId") Integer userId, @Param("agrId") Integer agrId);

    /**
     * 删除收藏
     */
    @Delete("delete from t_agricollect  where userId =#{userId} and agrId=#{agrId}")
    Integer delByUseIdAndAfrid(@Param("userId") Integer userId, @Param("agrId") Integer agrId);


    /**
     * 获取所有农情动态
     */
//    @Select("SELECT  t1.id,t1.descript,\n" +
//            "t3.id as cropId,t3.name as cropName,\n" +
//            "t8.id as pubertyId,t8.puberty as pubertyName,\n" +
//            "t2.showName as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,date_format(t1.createtime ,'%Y-%m-%d %H:%i:%S') as publisherTime, t1.longitude,t1.latitude,count(distinct t4.id)AS sumPrais, COUNT(DISTINCT t5.id) AS isPraise,COUNT(DISTINCT t6.id) AS iscollect\n" +
//            "            ,   group_concat(distinct t7.imageURLContent) as agrImages  \n" +
//            "             from  t_agrinfo t1\n" +
//            "            inner join t_user t2 on t1.userId = t2.id and t1.areacode like concat(#{areaCode},'%')\n" +
//            "            LEFT JOIN t_crop t3 on t1.breed  = t3.id\n" +
//            "            LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
//            "            LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and  t5.userId= #{userId}\n" +
//            "            LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n" +
//            "            Left join t_agrinfoimg t7 on  t1.id = t7.agrInfoId\n" +
//            "            Left join t_puberty t8 on t1.growthperiod = t8.id\n" +
//            "            group by t1.id\n" +
//            "            ORDER BY t1.createtime DESC" +
//            " limit #{pageSize},#{pageNum} ")
//    List<AgrCollect> getAllListAgrInfo(@Param("areaCode") String areaCode,@Param("userId") String userId,@Param("pageSize")int pageSize ,@Param("pageNum") int pageNum);

    @SelectProvider(type = AgrCollectSql.class, method = "agrInfoDynamic")
    List<AgrCollect> getAllListAgrInfo(@Param("farmId") String farmId, @Param("areaCode") String areaCode, @Param("userId") String userId,@Param("pageSize")int pageSize ,@Param("pageNum") int pageNum,@Param("beginTime")String beginTime,@Param("endTime")String endTime);


    /**
     * 获取所有农情动态总数
     */
//    @Select("select count(1) from (SELECT  count(distinct t1.id) from  t_agrinfo t1\n" +
//            "inner join t_user t2 on t1.userId = t2.id and t1.areacode like concat(#{areaCode},'%')\n" +
//            "LEFT JOIN t_crop t3 on t1.`type`  = t3.code \n" +
//            "LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
//            "LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and  t5.userId= #{userId}\n" +
//            "LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n" +
//            "group by t1.id\n" +
//            "ORDER BY t1.createtime DESC) aqua  ")
    @SelectProvider(type = getAllListAgrInfoCount.class,method = "getAllListAgrInfoCountSumSQL")
    int getAllListAgrInfoCountSum(@Param("areaCode") String areaCode, @Param("userId") String userId);
    class  getAllListAgrInfoCount{
        public String getAllListAgrInfoCountSumSQL(Map<String, Object> map) {
            String areaCode = (String) map.get("areaCode");
            String userId = (String) map.get("userId");
            StringBuffer stringBuffer = new StringBuffer();
            stringBuffer.append(" select count(1) from (SELECT  count(distinct t1.id) from  t_agrinfo t1\n" +
                    "            inner join t_user t2 on t1.userId = t2.id  ");
            if(areaCode!=null){
                String areaCodes[]=areaCode.split(",");
                stringBuffer.append(" and (  ");
                String strs="";
                for(String str:areaCodes){
                    strs+="   t1.areacode like  '" + str + "%' or  ";
                }
                stringBuffer.append(strs.substring(0,strs.length()-4));
                stringBuffer.append("  ) ");
            }
            stringBuffer.append("   LEFT JOIN t_crop t3 on t1.`type`  = t3.code \n" +
                    "            LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
                    "            LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId  ");

            if(userId!=null){
                stringBuffer.append(" and  t5.userId= "+userId+" ");
            }
            stringBuffer.append(" LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId ");
            if(userId!=null){
                stringBuffer.append(" and t6.userId= "+userId+" ");
            }
            stringBuffer.append("  group by t1.id ORDER BY t1.createtime DESC) aqua ");
            return  stringBuffer.toString();
        }
    }



    /**
     * 获取我的农情
     */
    @Select("SELECT  t1.id,t1.descript,\n" +
            "t3.id as cropId,t3.name as cropName,\n" +
            "t8.id as pubertyId,t8.puberty as pubertyName,\n" +
            "t2.showName as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,date_format(t1.createtime ,'%Y-%m-%d %H:%i:%S') as publisherTime, t1.longitude,t1.latitude,count(distinct t4.id)AS sumPrais, COUNT(DISTINCT t5.id) AS isPraise,COUNT(DISTINCT t6.id) AS iscollect\n" +
            "            ,   group_concat(distinct t7.imageURLContent) as agrImages  \n" +
            "             from  t_agrinfo t1  " +
            "inner join t_user t2 on t1.userId = t2.id and t1.userId= #{userId} \n" +
            "LEFT JOIN t_crop t3 on t1.`type`  = t3.code  \n" +
            "LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId \n" +
            "LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and  t5.userId= #{userId}\n" +
            "LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n " +
            "  Left join t_agrinfoimg t7 on t7.agrInfoId=t1.id " +
            "   Left join t_puberty t8 on t1.growthperiod = t8.id\n" +
            "group by t1.id\n" +
            "ORDER BY t1.createtime DESC limit #{pageSize},#{pageNum} ")
    List<AgrCollect> getMySelfAllListAgrInfo(@Param("userId") String userId, @Param("pageSize") int pageSize, @Param("pageNum") int pageNum);

    /**
     * 获取我的农情总数
     */
    @Select("select count(1) from (SELECT  count(1) from  t_agrinfo t1\n" +
            "inner join t_user t2 on t1.userId = t2.id and t1.userId= #{userId} \n" +
            "LEFT JOIN t_crop t3 on t1.`type`  = t3.code  \n" +
            "LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId \n" +
            "LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and  t5.userId= #{userId}\n" +
            "LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n" +
            "group by t1.id\n" +
            "ORDER BY t1.createtime DESC)aqua  ")
    int getMySelfAllListAgrInfoCountSum(@Param("userId") String userId);


    /**
     * 获取我的收藏的农情
     */
    @Select("SELECT  t1.id,t1.descript,\n" +
            "t3.id as cropId,t3.name as cropName,\n" +
            "t8.id as pubertyId,t8.puberty as pubertyName,\n" +
            "t2.showName as publisherName,t2.address as publisherAddress,t2.HeadImage as publisherHeadImage,date_format(t1.createtime ,'%Y-%m-%d %H:%i:%S') as publisherTime, t1.longitude,t1.latitude,count(distinct t4.id)AS sumPrais, COUNT(DISTINCT t5.id) AS isPraise,COUNT(DISTINCT t6.id) AS iscollect\n" +
            "            ,   group_concat(distinct t7.imageURLContent) as agrImages  \n" +
            "             from  t_agrinfo t1  " +
            "inner join t_user t2 on t1.userId = t2.id  \n" +
            "LEFT JOIN t_crop t3 on t1.`type`  = t3.code \n" +
            "LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
            "LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and t5.userId= #{userId}\n" +
            "inner JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n" +
            " Left join t_agrinfoimg t7 on t7.agrInfoId=t1.id " +
            "   Left join t_puberty t8 on t1.growthperiod = t8.id  " +
            "group by t1.id\n " +
            " ORDER BY t1.createtime DESC limit #{pageSize},#{pageNum}")
    List<AgrCollect> getMySelfCollectListAgrInfo(@Param("userId") String userId, @Param("pageSize") int pageSize, @Param("pageNum") int pageNum);


    /**
     * 获取我的收藏的农情总数
     */
    @Select("select count(1) from (SELECT count(1) from  t_agrinfo t1\n" +
            "inner join t_user t2 on t1.userId = t2.id  \n" +
            "LEFT JOIN t_crop t3 on t1.`type`  = t3.code \n" +
            "LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
            "LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId and t5.userId= #{userId}\n" +
            "inner JOIN t_agricollect t6 ON t1.id = t6.agrId and t6.userId= #{userId}\n" +
            "group by t1.id\n" +
            "ORDER BY t1.createtime DESC) aqua ")
    int getMySelfCollectListAgrInfoCountSum(@Param("userId") String userId);

    /**
     * 获取农情互动
     */
    @Select("select * from \n" +
            "(SELECT t1.agrInfoId,t2.showName,t2.id as userId,t2.HeadImage, t1.`comment`, date_format(t1.commentTime,'%Y-%m-%d %H:%i:%S') as commentTime,t1.id from  t_agrinfocomment t1 \n" +
            "inner join t_user t2 on t1.userId  = t2.id where t1.agrInfoId = #{agrinfID} and t1.parentId = -1 and t2.del_flg = 0  ) tt1\n" +
            "LEFT join \n" +
            "(SELECT t1.agrInfoId,t2.showName,  t1.`comment`,t1.commentTime ,t1.parentId from t_agrinfocomment t1 \n" +
            "inner join t_user t2 on t1.userId  = t2.id where t2.del_flg = 0) tt2\n" +
            "on tt1.id = tt2.parentId\n" +
            "order by tt2.commentTime ")
    List<Map<String, Object>> getListInteract(@Param("agrinfID") String agrinfID);


    public class AgrCollectSql {
        public String agrInfoDynamic(Map<String, Object> map) {
            String farmId =(String) map.get("farmId");
            String areaCode = (String)map.get("areaCode");
            String userId = (String)map.get("userId");
            String beginTime = (String)map.get("beginTime");
            String endTime = (String)map.get("endTime");
            Integer pageSize = (int)map.get("pageSize");
            Integer pageNum = (int)map.get("pageNum");

            StringBuffer sql1 = new StringBuffer();
            StringBuffer sql2 = new StringBuffer();
            StringBuffer sql3 = new StringBuffer();
            StringBuffer sql4 = new StringBuffer();

            sql1.append("SELECT\n" +
                    "\t distinct t1.id,\n" +
                    "\tt1.descript,\n" +
                    "\tt3.id AS cropId,\n" +
                    "\tt3. NAME AS cropName,\n" +
                    "\tt8.id AS pubertyId,\n" +
                    "\tt8.puberty AS pubertyName,\n" +
                    "\tt2.showName AS publisherName,\n" +
                    "\tt2.address AS publisherAddress,\n" +
                    "\tt2.HeadImage AS publisherHeadImage,\n" +
                    "\tdate_format(\n" +
                    "\t\tt1.createtime,\n" +
                    "\t\t'%Y-%m-%d %H:%i:%S'\n" +
                    "\t) AS publisherTime,\n" +
                    "\tt1.longitude,\n" +
                    "\tt1.latitude,\n" +
                    "\tcount(DISTINCT t4.id) AS sumPrais,\n" +
                    "\tCOUNT(DISTINCT t5.id) AS isPraise,\n" +
                    "\tCOUNT(DISTINCT t6.id) AS iscollect \n" +
                    "                        ,   group_concat(distinct t7.imageURLContent) as agrImages\n" +
                    "            from  t_agrinfo t1\n" +
                    "          inner join t_user t2 on t1.userId = t2.id ");
            if (areaCode != null && !"".equals(areaCode)) {
                String areaCodes[]=areaCode.split(",");
                sql1.append(" and (  ");
                String strs="";
                for(String str:areaCodes){
                    strs+="   t1.areacode like  '" + str + "%' or  ";
                }
                sql1.append(strs.substring(0,strs.length()-4));
                sql1.append("  ) ");

            }
            if(beginTime!=null &&endTime!=null ){
                sql1.append(" and t1.createtime between '"+beginTime+"' and '"+endTime+"'  ");
            }
            sql2.append(" LEFT JOIN t_crop t3 on t1.breed  = t3.id\n" +
                    "           LEFT JOIN t_agrpraise t4 ON t1.id = t4.agrInfoId\n" +
                    "           LEFT JOIN t_agrpraise t5 ON t1.id = t5.agrInfoId ");
            if (userId != null && !"".equals(userId)) {
                sql2.append(" and  t5.userId= '" + userId + "' ");
            }
            sql3.append(" LEFT JOIN t_agricollect t6 ON t1.id = t6.agrId ");
            if (userId != null && !"".equals(userId)) {
                sql3.append(" and t6.userId='" + userId + "' ");
            }
            sql4.append(" LEFT JOIN t_agrinfoimg t7 ON t1.id = t7.agrInfoId\n" +
                    "LEFT JOIN t_puberty t8 ON t1.growthperiod = t8.id where 1=1 ");
            if (farmId != null && !"".equals(farmId)) {
                sql4.append(" and t1.farm ='"+farmId+"' ");
            }
            sql4.append(" group by t1.id ORDER BY t1.createtime DESC limit " + pageSize+","+pageNum+";");
            return sql1.toString()+sql2.toString()+sql3.toString()+sql4.toString();
        }
    }

}
